Introduction into Pandas
There is often some confusion about whether Pandas is an alternative to Numpy, SciPy and Matplotlib. The truth is that it is built on top of Numpy. This means that Numpy is required by pandas. Scipy and Matplotlib on the other hand are not required by pandas but they are extremely useful. That's why the Pandas project lists them as "optional dependency".
Pandas is a software library written for the Python programming language. It is used for data manipulation and analysis. It provides special data structures and operations for the manipulation of numerical tables and time series. Pandas is free software released under the three-clause BSD license.
Data Structures
We will start with the following two important data structures of Pandas:
- Series and
- DataFrame
Series
A Series is a one-dimensional array-like object containing an array of data, which can be any NumPy data type, and an associated array of data labels, functioning as its index.
A simple example:
import pandas as pd S = pd.Series([11, 28, 72, 3, 5, 8]) SOutput:
0 11 1 28 2 72 3 3 4 5 5 8 dtype: int64
We can see two columns: The indices on the left side and the values on the right side. Pandas uses a default indexing starting with 0 going to 5, which is the length of the data minus 1.
print(S.index) print(S.values)Output:
Int64Index([0, 1, 2, 3, 4, 5], dtype='int64') [11 28 72 3 5 8]
If we compare this to creating an array in numpy, there are still lots of similarities:
import numpy as np X = np.array([11, 28, 72, 3, 5, 8]) print(X) print(S.values) list(S.values) == list(X)Output:
[11 28 72 3 5 8] [11 28 72 3 5 8]Output:
True
It's possible to create an individual index instead of the default index:
fruits = ['apples', 'oranges', 'cherries', 'pears'] quantities = [20, 33, 52, 10] S = pd.Series(quantities, index=fruits) SOutput:
apples 20 oranges 33 cherries 52 pears 10 dtype: int64
A big advantage to NumPy arrays is obvious from the previous example: We can use arbitrary indices.
It's possible to select both single values or more than one value of a Series:
print(S['apples'])Output:
20
print(S[['apples', 'oranges', 'cherries']])Output:
apples 20 oranges 33 cherries 52 dtype: int64
It's possible to scalar operations or mathematical functions on a series
import numpy as np print((S + 3) * 4) print("======================") np.sin(S)Output:
apples 92 oranges 144 cherries 220 pears 52 dtype: int64 ======================Output:
apples 0.912945 oranges 0.999912 cherries 0.986628 pears -0.544021 dtype: float64
Filtering with a boolean array:
S[S>30] SOutput:
apples 20 oranges 33 cherries 52 pears 10 dtype: int64
A series can be seen as an ordered dictionary with a fixed length.
"apples" in SOutput:
True
We can even pass a dictionary to a Series object, when we create it. We get a Series with the dict's keys as the indices. The indices will be sorted.
cities = {"London": 8615246, "Berlin": 3562166, "Madrid": 3165235, "Rome": 2874038, "Paris": 2273305, "Vienna": 1805681, "Bucharest":1803425, "Hamburg": 1760433, "Budapest": 1754000, "Warsaw": 1740119, "Barcelona":1602386, "Munich": 1493900, "Milan": 1350680} city_series = pd.Series(cities) print(city_series)Output:
Barcelona 1602386 Berlin 3562166 Bucharest 1803425 Budapest 1754000 Hamburg 1760433 London 8615246 Madrid 3165235 Milan 1350680 Munich 1493900 Paris 2273305 Rome 2874038 Vienna 1805681 Warsaw 1740119 dtype: int64
We have already seen that we can pass a list or a tuple to the keyword argument 'index'. In this case, the list (or tuple) passed to index might not be equal to the keys, e.g. there may be less or more entries in index:
my_cities = ["London", "Paris", "Zurich", "Berlin", "Stuttgart", "Hamburg"] my_city_series = pd.Series(cities, index=my_cities) print(my_city_series)Output:
London 8615246 Paris 2273305 Zurich NaN Berlin 3562166 Stuttgart NaN Hamburg 1760433 dtype: float64
We can see, that the cities, which are not included in the dictionary, get the value NaN assigned. NaN stands for "not a number". It can also be seen as meaning "missing" in our example.
We can check for missing values with the methods isnull and notnull:
print(my_city_series.isnull())Output:
London False Paris False Zurich True Berlin False Stuttgart True Hamburg False dtype: bool
print(my_city_series.notnull())Output:
London True Paris True Zurich False Berlin True Stuttgart False Hamburg True dtype: bool
We get also a NaN, if a value in the dictionary has a None:
d = {"a":23, "b":45, "c":None, "d":0} S = pd.Series(d) print(S)Output:
a 23 b 45 c NaN d 0 dtype: float64
pd.isnull(S)Output:
a False b False c True d False dtype: bool
pd.notnull(S)Output:
a True b True c False d True dtype: bool
DataFrame
The underlying idea of a DataFrame is based on spreadsheets. We can see the data structure of a DataFrame as tabular and spreadsheet-like. It contains an ordered collection of columns. Each column consists of a unique data typye, but different columns can have different types, e.g. the first column may consist of integers, while the second one consists of boolean values and so on.
A DataFrame has a row and column index; it's like a dict of Series with a common index.
cities = {"name": ["London", "Berlin", "Madrid", "Rome", "Paris", "Vienna", "Bucharest", "Hamburg", "Budapest", "Warsaw", "Barcelona", "Munich", "Milan"], "population": [8615246, 3562166, 3165235, 2874038, 2273305, 1805681, 1803425, 1760433, 1754000, 1740119, 1602386, 1493900, 1350680], "country": ["England", "Germany", "Spain", "Italy", "France", "Austria", "Romania", "Germany", "Hungary", "Poland", "Spain", "Germany", "Italy"]} city_frame = pd.DataFrame(cities) print(city_frame)Output:
country name population 0 England London 8615246 1 Germany Berlin 3562166 2 Spain Madrid 3165235 3 Italy Rome 2874038 4 France Paris 2273305 5 Austria Vienna 1805681 6 Romania Bucharest 1803425 7 Germany Hamburg 1760433 8 Hungary Budapest 1754000 9 Poland Warsaw 1740119 10 Spain Barcelona 1602386 11 Germany Munich 1493900 12 Italy Milan 1350680 [13 rows x 3 columns]
We can see that an index (0,1,2, ...) has been automatically assigned to the DataFrame. We can also assign a custom index to the DataFrame object:
ordinals = ["first", "second", "third", "fourth", "fifth", "sixth", "seventh", "eigth", "ninth", "tenth", "eleventh", "twelvth", "thirteenth"] city_frame = pd.DataFrame(cities, index=ordinals) print(city_frame)Output:
country name population first England London 8615246 second Germany Berlin 3562166 third Spain Madrid 3165235 fourth Italy Rome 2874038 fifth France Paris 2273305 sixth Austria Vienna 1805681 seventh Romania Bucharest 1803425 eigth Germany Hamburg 1760433 ninth Hungary Budapest 1754000 tenth Poland Warsaw 1740119 eleventh Spain Barcelona 1602386 twelvth Germany Munich 1493900 thirteenth Italy Milan 1350680 [13 rows x 3 columns]
We can also define or rearrange the order of the columns.
city_frame = pd.DataFrame(cities, columns=["name", "country", "population"], index=ordinals) print(city_frame)Output:
name country population first London England 8615246 second Berlin Germany 3562166 third Madrid Spain 3165235 fourth Rome Italy 2874038 fifth Paris France 2273305 sixth Vienna Austria 1805681 seventh Bucharest Romania 1803425 eigth Hamburg Germany 1760433 ninth Budapest Hungary 1754000 tenth Warsaw Poland 1740119 eleventh Barcelona Spain 1602386 twelvth Munich Germany 1493900 thirteenth Milan Italy 1350680 [13 rows x 3 columns]
We can calculate the sum of all the columns of a DataFrame or the sum of certain columns:
city_frame.sum()Output:
name LondonBerlinMadridRomeParisViennaBucharestHamb... country EnglandGermanySpainItalyFranceAustriaRomaniaGe... population 33800614 dtype: object
city_frame["population"].sum()Output:
33800614
We can use "cumsum" to calculate the cumulative sum:
x = city_frame["population"].cumsum() print(x)Output:
first 8615246 second 12177412 third 15342647 fourth 18216685 fifth 20489990 sixth 22295671 seventh 24099096 eigth 25859529 ninth 27613529 tenth 29353648 eleventh 30956034 twelvth 32449934 thirteenth 33800614 Name: population, dtype: int64
x is a Pandas Series. We can reassign it to the population column:
city_frame["population"] = x print(city_frame)Output:
name country population first London England 8615246 second Berlin Germany 12177412 third Madrid Spain 15342647 fourth Rome Italy 18216685 fifth Paris France 20489990 sixth Vienna Austria 22295671 seventh Bucharest Romania 24099096 eigth Hamburg Germany 25859529 ninth Budapest Hungary 27613529 tenth Warsaw Poland 29353648 eleventh Barcelona Spain 30956034 twelvth Munich Germany 32449934 thirteenth Milan Italy 33800614 [13 rows x 3 columns]
We can also include a column name which is not contained in the dictionary. In this case, all the values of this column will be set to NaN:
city_frame = pd.DataFrame(cities, columns=["name", "country", "area", "population"], index=ordinals) print(city_frame)Output:
name country area population first London England NaN 8615246 second Berlin Germany NaN 3562166 third Madrid Spain NaN 3165235 fourth Rome Italy NaN 2874038 fifth Paris France NaN 2273305 sixth Vienna Austria NaN 1805681 seventh Bucharest Romania NaN 1803425 eigth Hamburg Germany NaN 1760433 ninth Budapest Hungary NaN 1754000 tenth Warsaw Poland NaN 1740119 eleventh Barcelona Spain NaN 1602386 twelvth Munich Germany NaN 1493900 thirteenth Milan Italy NaN 1350680 [13 rows x 4 columns]
There are two ways to access a column of a DataFrame. The result is in both cases a Series:
# in a dictionary-like way: print(city_frame["population"])Output:
first 8615246 second 3562166 third 3165235 fourth 2874038 fifth 2273305 sixth 1805681 seventh 1803425 eigth 1760433 ninth 1754000 tenth 1740119 eleventh 1602386 twelvth 1493900 thirteenth 1350680 Name: population, dtype: int64
# as an attribute print(city_frame.population)Output:
first 8615246 second 3562166 third 3165235 fourth 2874038 fifth 2273305 sixth 1805681 seventh 1803425 eigth 1760433 ninth 1754000 tenth 1740119 eleventh 1602386 twelvth 1493900 thirteenth 1350680 Name: population, dtype: int64
print(type(city_frame.population))Output:
<class 'pandas.core.series.Series'>
p = city_frame.population p["first"] = 9000000 print(city_frame)Output:
name country area population first London England NaN 9000000 second Berlin Germany NaN 3562166 third Madrid Spain NaN 3165235 fourth Rome Italy NaN 2874038 fifth Paris France NaN 2273305 sixth Vienna Austria NaN 1805681 seventh Bucharest Romania NaN 1803425 eigth Hamburg Germany NaN 1760433 ninth Budapest Hungary NaN 1754000 tenth Warsaw Poland NaN 1740119 eleventh Barcelona Spain NaN 1602386 twelvth Munich Germany NaN 1493900 thirteenth Milan Italy NaN 1350680 [13 rows x 4 columns]
From the previous example, we can see that we have not copied the population column. "p" is a view on the data of city_frame.
We can also access the rows directly. We access the info of the fourth city in the following way:
city_frame.ix['fourth']Output:
name Rome country Italy area NaN population 2874038 Name: fourth, dtype: object
The column area is still not defined. We can set all elements of the column to the same value:
city_frame["area"] = 1572 print(city_frame)Output:
name country area population first London England 1572 9000000 second Berlin Germany 1572 3562166 third Madrid Spain 1572 3165235 fourth Rome Italy 1572 2874038 fifth Paris France 1572 2273305 sixth Vienna Austria 1572 1805681 seventh Bucharest Romania 1572 1803425 eigth Hamburg Germany 1572 1760433 ninth Budapest Hungary 1572 1754000 tenth Warsaw Poland 1572 1740119 eleventh Barcelona Spain 1572 1602386 twelvth Munich Germany 1572 1493900 thirteenth Milan Italy 1572 1350680 [13 rows x 4 columns]
In this case, it will be definitely better to assign the exact area to the cities. The list with the area values needs to have the same length as the number of rows in our DataFrame.
# area in square km: area = [1572, 891.85, 605.77, 1285, 105.4, 414.6, 228, 755, 525.2, 517, 101.9, 310.4, 181.8] city_frame["area"] = area print(city_frame)Output:
name country area population first London England 1572.00 9000000 second Berlin Germany 891.85 3562166 third Madrid Spain 605.77 3165235 fourth Rome Italy 1285.00 2874038 fifth Paris France 105.40 2273305 sixth Vienna Austria 414.60 1805681 seventh Bucharest Romania 228.00 1803425 eigth Hamburg Germany 755.00 1760433 ninth Budapest Hungary 525.20 1754000 tenth Warsaw Poland 517.00 1740119 eleventh Barcelona Spain 101.90 1602386 twelvth Munich Germany 310.40 1493900 thirteenth Milan Italy 181.80 1350680 [13 rows x 4 columns]
Let's sort our DataFrame according to the city area:
city_frame = city_frame.sort(columns="area", ascending=False) print(city_frame)Output:
name country area population first London England 1572.00 9000000 fourth Rome Italy 1285.00 2874038 second Berlin Germany 891.85 3562166 eigth Hamburg Germany 755.00 1760433 third Madrid Spain 605.77 3165235 ninth Budapest Hungary 525.20 1754000 tenth Warsaw Poland 517.00 1740119 sixth Vienna Austria 414.60 1805681 twelvth Munich Germany 310.40 1493900 seventh Bucharest Romania 228.00 1803425 thirteenth Milan Italy 181.80 1350680 fifth Paris France 105.40 2273305 eleventh Barcelona Spain 101.90 1602386 [13 rows x 4 columns]
Let's assume, we have only the areas of London, Hamburg and Milan. The areas are in a series with the correct indices. We can assign this series as well:
city_frame = pd.DataFrame(cities, columns=["name", "country", "area", "population"], index=ordinals) some_areas = pd.Series([1572, 755, 181.8], index=['first', 'eigth', 'thirteenth']) city_frame['area'] = some_areas print(city_frame)Output:
name country area population first London England 1572.0 8615246 second Berlin Germany NaN 3562166 third Madrid Spain NaN 3165235 fourth Rome Italy NaN 2874038 fifth Paris France NaN 2273305 sixth Vienna Austria NaN 1805681 seventh Bucharest Romania NaN 1803425 eigth Hamburg Germany 755.0 1760433 ninth Budapest Hungary NaN 1754000 tenth Warsaw Poland NaN 1740119 eleventh Barcelona Spain NaN 1602386 twelvth Munich Germany NaN 1493900 thirteenth Milan Italy 181.8 1350680 [13 rows x 4 columns]
A nested dictionary of dicts can be passed to a DataFrame as well. The indices of the outer dictionary are taken as the the columns and the inner keys. i.e. the keys of the nested dictionaries, are used as the row indices:
growth = {"Switzerland": {"2010": 3.0, "2011": 1.8, "2012": 1.1, "2013": 1.9}, "Germany": {"2010": 4.1, "2011": 3.6, "2012": 0.4, "2013": 0.1}, "France": {"2010":2.0, "2011":2.1, "2012": 0.3, "2013": 0.3}, "Greece": {"2010":-5.4, "2011":-8.9, "2012":-6.6, "2013": -3.3}, "Italy": {"2010":1.7, "2011": 0.6, "2012":-2.3, "2013":-1.9} }
growth_frame = pd.DataFrame(growth) growth_frameOutput:
| France | Germany | Greece | Italy | Switzerland | |
|---|---|---|---|---|---|
| 2010 | 2.0 | 4.1 | -5.4 | 1.7 | 3.0 |
| 2011 | 2.1 | 3.6 | -8.9 | 0.6 | 1.8 |
| 2012 | 0.3 | 0.4 | -6.6 | -2.3 | 1.1 |
| 2013 | 0.3 | 0.1 | -3.3 | -1.9 | 1.9 |
4 rows × 5 columns
You like to have the years in the columns and the countries in the rows? No problem, you can transpose the data:
growth_frame.TOutput:
| 2010 | 2011 | 2012 | 2013 | |
|---|---|---|---|---|
| France | 2.0 | 2.1 | 0.3 | 0.3 |
| Germany | 4.1 | 3.6 | 0.4 | 0.1 |
| Greece | -5.4 | -8.9 | -6.6 | -3.3 |
| Italy | 1.7 | 0.6 | -2.3 | -1.9 |
| Switzerland | 3.0 | 1.8 | 1.1 | 1.9 |
5 rows × 4 columns
growth_frame = pd.DataFrame(growth) growth_frame.reindex(["2013", "2012", "2011", "2010"])Output:
| France | Germany | Greece | Italy | Switzerland | |
|---|---|---|---|---|---|
| 2013 | 0.3 | 0.1 | -3.3 | -1.9 | 1.9 |
| 2012 | 0.3 | 0.4 | -6.6 | -2.3 | 1.1 |
| 2011 | 2.1 | 3.6 | -8.9 | 0.6 | 1.8 |
| 2010 | 2.0 | 4.1 | -5.4 | 1.7 | 3.0 |
4 rows × 5 columns
Filling a DataFrame with random values:
df = pd.DataFrame(np.random.randn(10, 5), columns=['a', 'b', 'c', 'd', 'e']) dfOutput:
| a | b | c | d | e | |
|---|---|---|---|---|---|
| 0 | 0.120591 | -0.919881 | 0.215054 | -0.596307 | -0.482016 |
| 1 | -0.101367 | -0.327798 | -0.193926 | -1.019803 | -0.409547 |
| 2 | 0.070791 | 0.049981 | 0.808494 | 1.024858 | 0.437878 |
| 3 | 1.198251 | -0.153978 | 1.742216 | -2.068733 | -1.192922 |
| 4 | -0.451963 | -0.717011 | 1.399723 | -1.175493 | 1.396906 |
| 5 | -1.991932 | 1.816324 | -0.599968 | 0.124527 | 0.286920 |
| 6 | -0.068553 | -0.352935 | 1.618726 | -1.102455 | -0.093867 |
| 7 | 1.050722 | 1.293406 | 0.297897 | 1.930885 | 1.003767 |
| 8 | 0.149615 | 0.445557 | -0.552403 | -0.256427 | -0.600788 |
| 9 | 1.306989 | 0.742940 | 0.734419 | -0.756281 | 0.654875 |
10 rows × 5 columns
We want to read in a csv file with the population data of all countries (July 2014). The delimiter of the file a a space and commas are used to separate groups of thousands in the numbers:
pop = pd.read_csv("countries_population.csv", quotechar="'", sep=" ", thousands=",") popOutput:
| China | 1,355,692,576 | |
|---|---|---|
| 0 | India | 1236344631 |
| 1 | European Union | 511434812 |
| 2 | United States | 318892103 |
| 3 | Indonesia | 253609643 |
| 4 | Brazil | 202656788 |
| 5 | Pakistan | 196174380 |
| 6 | Nigeria | 177155754 |
| 7 | Bangladesh | 166280712 |
| 8 | Russia | 142470272 |
| 9 | Japan | 127103388 |
| 10 | Mexico | 120286655 |
| 11 | Philippines | 107668231 |
| 12 | Ethiopia | 96633458 |
| 13 | Vietnam | 93421835 |
| 14 | Egypt | 86895099 |
| 15 | Turkey | 81619392 |
| 16 | Germany | 80996685 |
| 17 | Iran | 80840713 |
| 18 | Congo, Democratic Republic of the | 77433744 |
| 19 | Thailand | 67741401 |
| 20 | France | 66259012 |
| 21 | United Kingdom | 63742977 |
| 22 | Italy | 61680122 |
| 23 | Burma | 55746253 |
| 24 | Tanzania | 49639138 |
| 25 | Korea, South | 49039986 |
| 26 | South Africa | 48375645 |
| 27 | Spain | 47737941 |
| 28 | Colombia | 46245297 |
| 29 | Kenya | 45010056 |
| 30 | Ukraine | 44291413 |
| 31 | Argentina | 43024374 |
| 32 | Algeria | 38813722 |
| 33 | Poland | 38346279 |
| 34 | Uganda | 35918915 |
| 35 | Sudan | 35482233 |
| 36 | Canada | 34834841 |
| 37 | Morocco | 32987206 |
| 38 | Iraq | 32585692 |
| 39 | Afghanistan | 31822848 |
| 40 | Nepal | 30986975 |
| 41 | Peru | 30147935 |
| 42 | Malaysia | 30073353 |
| 43 | Uzbekistan | 28929716 |
| 44 | Venezuela | 28868486 |
| 45 | Saudi Arabia | 27345986 |
| 46 | Yemen | 26052966 |
| 47 | Ghana | 25758108 |
| 48 | Korea, North | 24851627 |
| 49 | Mozambique | 24692144 |
| 50 | Taiwan | 23359928 |
| 51 | Madagascar | 23201926 |
| 52 | Cameroon | 23130708 |
| 53 | Cote dIvoire' | 22848945 |
| 54 | Australia | 22507617 |
| 55 | Sri Lanka | 21866445 |
| 56 | Romania | 21729871 |
| 57 | Angola | 19088106 |
| 58 | Burkina Faso | 18365123 |
| 59 | Syria | 17951639 |
| ... | ... |
237 rows × 2 columns